ALTER proc proc_checkMadl 
@Madl varchar(6)
WITH encryption
as
-- NEU MA DAI LY HOP LE, TRA VE TENDAI LY
IF EXISTS( Select top 1 HopDong.Ten_DaiLy as Tendaily,Dmdonvi.Ten_dt as Tendv 
	from Hopdong inner join Dmdonvi ON Dmdonvi.Ma_DT = Left(Hopdong.Ma_dt,1) 
	Where Hopdong.isPublic=1 and Ma_DaiLy=@Madl
	-- dai ly chua thanh ly
	and Ngay_ThanhLy is null 
	--and HOPDONG.isTT = 0
	-- ma dai ly cu ko dc nhap lo to
	and ID not in (select HopDongID from tblChuyenDL)
	--order by HOPDONG.isTT
	)
BEGIN
	Select top 1 HopDong.Ten_DaiLy as Tendaily,Dmdonvi.Ten_dt as Tendv 
	from Hopdong inner join Dmdonvi ON Dmdonvi.Ma_DT = Left(Hopdong.Ma_dt,1) 
	Where Hopdong.isPublic=1 and Ma_DaiLy=@Madl
	-- dai ly chua thanh ly
	and Ngay_ThanhLy is null 
	--and HOPDONG.isTT = 0
	--order by HOPDONG.isTT
END
ELSE
BEGIN
-- neu chuyen dai ly,ma dai ly moi cho phep nhap lo to
	select hd.Ten_DaiLy as Tendaily,Dmdonvi.Ten_dt as Tendv from HOPDONG hd
	inner join Dmdonvi ON Dmdonvi.Ma_DT = Left(hd.Ma_dt,1) 
	where hd.isPublic=1 AND hd.ID = (
			select TOP 1 HopDongID from tblChuyenDL cdl
			where MaDLMoi = @Madl and cdl.Ngay <= GETDATE()
		ORDER BY cdl.Ngay
	)
END
go